<?php

include_once("DbManager.php");

class DBUtils extends DbManager
{
	protected $message;
	protected $DBTag;
	
	public function __construct()
	{
		parent::__construct();
	}
	
	public function insert($DBtag, $sql)
	{
		$returnId 	= 0;
		$conn 		= null;
		
		try{
			$conn 	= $this->getConnection($DBtag);
			$rs  	= mysql_query($sql, $conn) or die("<br><br>Invalid query: " . $sql . mysql_error($conn));

			if($rs){
				$returnId = mysql_insert_id($conn);
			}
		}catch(Exception $e){
			$this->message = $e->getMessage();
			
			echo mysql_error($conn) . $sql;
		}
		
		$this->freeConnection($DBtag, $conn);
			
		return $returnId;
	}
	

  	
  	/* insert a array data to table
  	 * $arrData = array(
			'trackingURL' 	=> $LinktoHomePage,
			'displayURL' 	=> $advertiserURL
		)
  	 * 
  	 */
  	function insertArray($DBtag, $table, $arrData, $sqlDuplicateKey = null) 
	{
	   	$id 	= 0;
		$conn 		= null;
		
		try{
			$conn 	= $this->getConnection($DBtag);
			
		   	reset($arrData);
		    
	      	$query = "INSERT INTO $table (";
	      	while (list($columnName, ) = each($arrData)) {
	        	$query .= $columnName . ', ';
	      	}
	      	
	      	$query = substr($query, 0, -2) . ') VALUES (';
	      	reset($arrData);
	      	while (list(, $value) = each($arrData)) {
	        	switch ((string)$value) {
	          		case 'now()':
	            		$query .= 'now(), ';
	            		break;
	            	case 'UTC_TIMESTAMP()':
	            		$query .= 'UTC_TIMESTAMP(), ';
	            		break;
	          		case 'null':
	            		$query .= 'null, ';
	            		break;
	          		default:
	            		$query .= "'" . addslashes($value) . "', ";
	            		break;
	        	}
	      	}
	      	
	      	$query  = substr($query, 0, -2);	      	
	      	$query .= ")";
	      	
	      	if($sqlDuplicateKey != null)      	
	      		$query .= $sqlDuplicateKey;		

		   	$rs  = mysql_query($query, $conn) or die("<br><br>Invalid query: " . $query . mysql_error($conn));

			if($rs){				
				$id = mysql_insert_id($conn);				
			}
		}catch(Exception $e){
			$this->message = $e->getMessage();
			
			echo mysql_error($conn) . $query;
		}
		
		$this->freeConnection($DBtag, $conn);
			
		return $id;
  	}
  	
    public function insertBatches($DBtag, $tableName, $columns, $rows, $batch_limit = 500, $sqlOdKey = null)
	{
		$dataCounter 	= 1;
		$insertCount 	= 0;
		$iColumnSize 	= count($columns);
		
		$strColumns		= implode (',', $columns);
		$sql			= "INSERT INTO $tableName ($strColumns) VALUES ";
        
        if(empty($sqlOdKey)){
	        $sqlOdKey 		= '';
	        
	        foreach($columns as $columnName){
	        	if(!empty($sqlOdKey))
	        		$sqlOdKey .= ",";
	        		
	            $sqlOdKey .= $columnName;
	            $sqlOdKey .= "=Values(";
	            $sqlOdKey .= $columnName;
	            $sqlOdKey .= ")";
	        }
	 
	 		$sqlOdKey 	= " ON DUPLICATE KEY UPDATE $sqlOdKey";
        }
 
   		$conn 		= null;
		try{
			$conn 	= $this->getConnection($DBtag);

            foreach($rows as $row){
    			$sql .= "(";
    			
    			foreach($row as $data){
    				$sql .= "'";
    				$sql .= addslashes($data);
        			$sql .= "',";
    			}
    			
    			$sql = substr($sql, 0, -1); // remove last comma   			
     
    			$sql .= ")";
 
    			if(($dataCounter % $batch_limit) == 0){
	 				$sql 	.= $sqlOdKey;
	 				
	 				$rs  	= mysql_query($sql, $conn);// or die("<br><br>Invalid query: " . $sql . mysql_error($conn));
	 				
	 				$insertCount += mysql_affected_rows($conn);
	 				
	 				$sql	= "INSERT INTO $tableName ($strColumns) VALUES ";
	 			}else{
	 				$sql	.= ",";
	 			}
    			
    			$dataCounter++;
            }
    		
    		$len = strlen($sql) - 1;
    		if(strrpos($sql, ',') == $len){
	 			$sql = substr($sql, 0, -1); // remove last comma
 				
 				$rs  	= mysql_query($sql, $conn);// or die("<br><br>Invalid query: " . $sql . mysql_error($conn));
	 				
	 			$insertCount += mysql_affected_rows($conn);
	 		}
		}catch(Exception $e){
			$this->message = $e->getMessage();
			
			echo mysql_error($conn) . $sql;
		}
		
		$this->freeConnection($DBtag, $conn);
		
		return $insertCount;
	}
	
	/* web list by condition
	 *
	 * @param  String DBtag # DB tag
	 * @param  String sql
	 * @return Array[][]
	 */
	public function update($DBtag, $sql)
	{
		$isreturn 	= false;
		$conn 		= null;
		
		try{
			$conn 	= $this->getConnection($DBtag);
			$rs  	= mysql_query($sql, $conn) or die("<br><br>Invalid query: " . $sql . mysql_error($conn));

			if($rs && mysql_affected_rows($conn) > 0){
				$isreturn = true;
			}
		}catch(Exception $e){
			$this->message = $e->getMessage();
			
			echo mysql_error($conn) . $sql;
		}
		
		$this->freeConnection($DBtag, $conn);
			
		return $isreturn;
	}

	public function updateDdl($DBtag, $sql)
	{
		$isreturn 	= false;
		$conn 		= null;
		
		try{
			$conn 	= $this->getConnection($DBtag);
			$rs  	= mysql_query($sql, $conn) or die("<br><br>Invalid query: " . $sql . mysql_error($conn));

			if($rs){
				$isreturn = true;
			}
		}catch(Exception $e){
			$this->message = $e->getMessage();
			
			echo mysql_error($conn) . $sql;
		}
		
		$this->freeConnection($DBtag, $conn);
			
		return $isreturn;
	}
		
	/* 
	 * @param $DBtag DB tag name
	 * @param $table mysql table name
	 * @param $data: array
	 */
	function updateArray($DBtag, $table, $arrData, $sqlDuplicateKey = null) 
	{
	   	$isreturn 	= 0;
		$conn 		= null;
		
		try{
			$conn 	= $this->getConnection($DBtag);
			
		   	reset($arrData);
		    
	      	$query = "UPDATE $table SET ";
	      	
	      	while (list($columns, $value) = each($arrData)) {
	        	switch ((string)$value) {
	          		case 'now()':
	            		$query .= $columns . ' = now(), ';
	            		break;
	            	case 'UTC_TIMESTAMP()':
	            		$query .= 'UTC_TIMESTAMP(), ';
	            		break;
	          		case 'null':
	            		$query .= $columns .= ' = null, ';
	            		break;
	          		default:
	            		$query .= $columns . " = '" . addslashes($value) . "', ";
	            		break;
	        	}
	      	}
	      	
	      	$query = substr($query, 0, -2) . ' WHERE ';
		   
			if($sqlDuplicateKey != null)      	
	      		$query .= $sqlDuplicateKey;
			
		   	$rs  = mysql_query($query, $conn);// or die("<br><br>Invalid query: " . $query . mysql_error($conn));

			if($rs){				
				$isreturn = mysqli_affected_rows($conn);				
			}
		}catch(Exception $e){
			$this->message = $e->getMessage();
			
			echo mysql_error($conn) . $query;
		}
		
		$this->freeConnection($DBtag, $conn);
			
		return $isreturn;
  	}
  	
		
	public function isExisted($DBtag, $sql)
	{
		$isReturn 	= false;
		$conn 		= null;
		
		try{
			$conn 	= $this->getConnection($DBtag);
			$rs  	= mysql_query($sql, $conn);

			if($rs && mysql_num_rows($rs) > 0){
				$isReturn 	= true;
			}
		}catch(Exception $e){
			$this->message = $e->getMessage();
			
			echo mysql_error($conn) . $sql;
		}
		
		$this->freeConnection($DBtag, $conn);
			
		return $isReturn;
	}
	
	public function getRow($DBtag, $sql)
	{
		$arrBack 	= array();
		$conn 		= null;
		
		try{
			$conn 	= $this->getConnection($DBtag);
			$rs  	= mysql_query($sql, $conn);

			if($rs && mysql_num_rows($rs) > 0){
				$arrBack = mysql_fetch_array($rs);
			}
			
			mysql_free_result($rs);
		}catch(Exception $e){
			$this->message = $e->getMessage();
			
			echo mysql_error($conn) . $sql;
		}
		
		$this->freeConnection($DBtag, $conn);
			
		return $arrBack;
	}
	
	public function getRows($DBtag, $sql)
	{
		$arrBack 	= array();
		$conn 		= null;
		
		try{
			$conn 	= $this->getConnection($DBtag);
			$rs  	= mysql_query($sql, $conn);

			if($rs){
				while($arrCache = mysql_fetch_array($rs)){
					$arrBack[] = $arrCache;
				}
			}
		}catch(Exception $e){
			$this->message = $e->getMessage();
			
			echo mysql_error($conn) . $sql;
		}
		
		$this->freeConnection($DBtag, $conn);
			
		return $arrBack;
	}
	
	/* web list by condition
	 *
	 * @param  String DBtag # DB tag
	 * @param  String sql
	 * @return Array[][]
	 */
	public function getColumn($DBtag, $sql)
	{
		$isreturn 	= '';
		$conn 		= null;
		
		try{
			$conn 	= $this->getConnection($DBtag);
			$rs  	= mysql_query($sql, $conn) or die("Invalid query: " . $sql . mysql_error($conn));

			if($arrCache = mysql_fetch_row($rs)){
				$isreturn = $arrCache[0];
			}
			
			mysql_free_result($rs);
		}catch(Exception $e){
			$this->message = $e->getMessage();
			
			echo mysql_error($conn) . $sql;
		}
		
		$this->freeConnection($DBtag, $conn);
			
		return $isreturn;
	}
	
	public function getColumns($DBtag, $sql)
	{
		$arrback 	= array();
		$conn 		= null;
		
		try{
			$conn 	= $this->getConnection($DBtag);
			$rs  	= mysql_query($sql, $conn) or die($sql . mysql_error($conn));

			while($arrCache = mysql_fetch_row($rs)){
				$arrback[] = $arrCache[0];
			}
		}catch(Exception $e){
			$this->message = $e->getMessage();
			
			echo mysql_error($conn) . $sql;
		}
		
		$this->freeConnection($DBtag, $conn);
			
		return $arrback;
	}
	
	/* web list by condition
	 *
	 * @param  String DBtag # DB tag
	 * @param  String sql
	 * @return Array[][]
	 */
	public function getHash($DBtag, $sql)
	{
		$arrBack 	= array();
		$conn 	= null;
		
		try{
			$conn = $this->getConnection($DBtag);

			$rs  = mysql_query($sql, $conn) or die($sql . mysql_error($conn));
			if($rs){
				while($arrCache = mysql_fetch_row($rs)){
					$arrBack[$arrCache[0]] = $arrCache[1];
				}
			}
		}catch(Exception $e){
			$this->message = $e->getMessage();
			
			echo mysql_error($conn) . $sql;
		}
		
		$this->freeConnection($DBtag, $conn);
			
		return $arrBack;
	}
	

	/* custom condition
	 *
	 * @param  string condition
	 * @return array
	 */
	public function getPageRows($DBTag, $columns, $where, $orders, $page = 0, $discount = 20, $url = '')
	{
		$arrback 	= array();
		$conn 		= null;
		 
		try{
			$conn = $this->getConnection($DBTag);
	
			$sql = 'SELECT count(*) FROM '.$where;

//			echo $sql;
			$rs  = mysql_query($sql, $conn);
			list($total) = mysql_fetch_row($rs);
			$this->mutilPager($total, $page, $discount, $url);
			
			if(!empty($total)){
				if(empty($discount))
					$sql = "SELECT $columns FROM $where";
				else{
					$sql = "SELECT $columns FROM $where $orders LIMIT $this->firstcount, $discount";
				}
				
				$rs  = mysql_query($sql, $conn);
				if($rs){
					while(is_array($arrtmp = mysql_fetch_array($rs))){
						$arrback[] = $arrtmp;
					}
				}
			}
		}catch(Exception $e){
			$this->message = $e->getMessage();
			
			echo mysql_error($conn) . $sql;
		}
		
		$this->freeConnection($DBTag, $conn);
			
		return $arrback;
	}
	
	public function getMessage() 
	{
		return $this->message;
	}

	
}

?>
